﻿param ([switch]$SendEmail = $false, [string]$OneOffEmailAddr = "default")

## This script produces an account report for CHW Exchange.  Key data for every user w/ mailbox is reported.
## David Ray - 9/17/2010

Function Convert-ToUACFlag ([int]$uac,[switch]$ToString) {
	
	$flags = @()
	switch ($uac)
	{
		{($uac -bor 0x0002) -eq $uac}    {$flags += "Disabled";break}
		{($uac -bor 0x0200) -eq $uac}    {$flags += "Enabled"}
#		{($uac -bor 0x0008) -eq $uac}    {$flags += "HOMEDIR_REQUIRED"}
#		{($uac -bor 0x0010) -eq $uac}    {$flags += "Locked-out"}
#		{($uac -bor 0x0020) -eq $uac}    {$flags += "No Password Required"}
#		{($uac -bor 0x0040) -eq $uac}    {$flags += "Cannot Change Password"}
#		{($uac -bor 0x0100) -eq $uac}    {$flags += "TEMP_DUPLICATE_ACCOUNT"}
#		{($uac -bor 0x0800) -eq $uac}    {$flags += "INTERDOMAIN_TRUST_ACCOUNT"}
#		{($uac -bor 0x1000) -eq $uac}    {$flags += "WORKSTATION_TRUST_ACCOUNT"}
#		{($uac -bor 0x2000) -eq $uac}    {$flags += "SERVER_TRUST_ACCOUNT"}
#		{($uac -bor 0x10000) -eq $uac}   {$flags += "No Password Expiration"}
#		{($uac -bor 0x0080) -eq $uac}    {$flags += "Encrypted Text Password"}
#		{($uac -bor 0x20000) -eq $uac}   {$flags += "Majority Node Logon Account"}
#		{($uac -bor 0x40000) -eq $uac}   {$flags += "SMARTCARD_REQUIRED"}
#		{($uac -bor 0x80000) -eq $uac}   {$flags += "Trusted for Delegation"}
#		{($uac -bor 0x100000) -eq $uac}  {$flags += "NOT_DELEGATED"}
#		{($uac -bor 0x200000) -eq $uac}  {$flags += "USE_DES_KEY_ONLY"}
#		{($uac -bor 0x400000) -eq $uac}  {$flags += "Don`'t Require Kerberos PreAuth"}
#		{($uac -bor 0x800000) -eq $uac}  {$flags += "Password Expired"}
#		{($uac -bor 0x1000000) -eq $uac} {$flags += "TRUSTED_TO_AUTH_FOR_DELEGATION"}
	}
	if($toString.ispresent){$flags | %{if($mystring){$mystring += ",$_"}else{$mystring = $_}};$mystring}else{$flags}
}

## Setup Variables
	$defaultCc = @("Rick_M@dell.com","Bill_Branson@dell.com")
	$defaultTo = @("John_G_Slaughter@dell.com")
	$defaultBcc = @("david_f_ray@dell.com,exchangeop@ps.net")
	$defaultFrom = "ExchangeOp@ps.net"
	$subject = "CHW Monthly Acccount Report"
	$emailMessage = "The attached file lists all CHW Mailbox-enabled users along with key data."
	$SMTPserver = "smtp-relay.chw.edu"
	$reportName = "CHWAccountReport" + $(Get-Date -Format "yyMMdd")
	$reportPath = "E:\Reports\CHWAccountReport"
	$org = "CHW"
	$site = "AZNV"
	$mailboxUsers = Get-Mailbox  -ResultSize unlimited
	$AccountReport = @()
	$UserReport = New-Object PSObject
	
	
	foreach ($user in $mailboxUsers) {
		"Inside Loop"
		$UserReport = New-Object PSObject
		$userData = Get-User $user
		$UserReport | Add-Member -MemberType NoteProperty -Name Organization -Value $org
		$UserReport | Add-Member -MemberType NoteProperty -Name Displayname -Value $user.displayname
		$UserReport | Add-Member -MemberType NoteProperty -Name Alias -Value $user.alias
		$UserReport | Add-Member -MemberType NoteProperty -Name NTAccount -Value $user.samaccountname
		$UserReport | Add-Member -MemberType NoteProperty -Name AccountType -Value $user.recipienttype
		$UserReport | Add-Member -MemberType NoteProperty -Name Hidden -Value $user.HiddenFromAddressListsEnabled
		$UserReport | Add-Member -MemberType NoteProperty -Name MailboxEmail -Value $user.WindowsEmailAddress
		$UserReport | Add-Member -MemberType NoteProperty -Name MailServer -Value $user.ServerName
		$UserReport | Add-Member -MemberType NoteProperty -Name Department -Value $userData.Department
		$UserReport | Add-Member -MemberType NoteProperty -Name SiteAdministrativeGroup -Value $site
		$UserReport | Add-Member -MemberType NoteProperty -Name ContainerOU -Value $user.OrganizationalUnit
		$UserReport | Add-Member -MemberType NoteProperty -Name Title -Value $userData.Title
		$UserReport | Add-Member -MemberType NoteProperty -Name City -Value $userData.City
		$UserReport | Add-Member -MemberType NoteProperty -Name Phone -Value $userData.Phone
		$UserReport | Add-Member -MemberType NoteProperty -Name CreationDate -Value $user.WhenCreated
		$UserReport | Add-Member -MemberType NoteProperty -Name DateLastModified -Value $user.WhenChanged
		$acctStatus = Convert-ToUACFlag $user.UserAccountControl.value__ -tostring
		$UserReport | Add-Member -MemberType NoteProperty -Name AccountStatus -Value $acctStatus
		$passwordAge = Get-QADUser -DontUseDefaultIncludedProperties $user.Alias | Select-Object PasswordAge
		if ($passwordAge.passwordage -eq $null) {$passwordLastSet = "Not Set"} else {$passwordLastSet = $passwordAge.PasswordAge.Days}
		$UserReport | Add-Member -MemberType NoteProperty -Name PasswordLastChange -Value $passwordLastSet
		$AccountReport += $UserReport
	}
	$AccountReport | Export-Csv -NoTypeInformation "$($reportPath)\$($reportName).csv"
	
	
	
	##
	## Save all this out to Excel too and try to prettify it some.
	##

	## The constants that we will use

	$xlCenter = -4108
	$xlCellValue = 1
	$xl3TrafficLights2 = 5
	$xl3Symbols = 7
	$xlConditionValueNumber = 0
	$xlIconSet = 6
	$xlAutomatic = -4105
	$xlBottom = -4107
	$xlContinuous = 1
	$xlThin = 2
	$xlBetween = 1
	$xlNotBetween = 2
	$xlEqual = 3
	$xlNotEqual = 4
	$xlGreater = 5
	$xlLess = 6
	$xlGreaterEqual = 7
	$xlLessEqual = 8
	$xlOpenXMLWorkbook = 51
	$xlToRight = -4161
	$xlExcel12 = 50
	
	# Create an instance of Excel
	$Excel = New-Object -comobject Excel.Application
	$Excel.visible = $True 
	$bucket = $Excel.Workbooks.Open("$($reportPath)\$($reportName).csv")  #$bucket is simply a variable to catch unwanted output so it doesn't go to screen (bit-bucket)
	$Worksheet = $Excel.Worksheets.Item(1)
	$Worksheet.Name = "CHW Account Report"
	


	# Add a little formatting to the column header
	$Range = $Worksheet.Range("A1",$Worksheet.Range("A1").End($xlToRight))
	$Style = $Info.UsedRange
	$Range.Interior.ColorIndex = 19
	$Range.Font.ColorIndex = 11
	$Range.Font.Bold = $True
	$Range.Borders.LineStyle = $xlContinuous
	$Range.Borders.ColorIndex = 0
	$Range.Borders.TintAndShade = 0
	$Range.Borders.Weight = $xlThin


	

	# Make Columns wide enough for content
	$bucket = $Worksheet.Range("A:R").entireColumn.Autofit()



	if (Test-Path "$($ReportPath)\$($ReportName).xlsb") {Remove-Item "$($ReportPath)\$($ReportName).xlsb"}
	$Excel.Workbooks.Item(1).SaveAs("$($ReportPath)\$($ReportName).xlsb",$xlExcel12)
	$Excel.Quit()
	$Info = $null
	$Workbook = $null
	$Worksheet = $null
	$Excel = $null
	[GC]::Collect()

	## Send Mail
	if ($sendEmail.ispresent) {
		Start-Sleep 20  #allow time for Excel to release the file for attachment
		$subject = "CHW Monthly Account Report"
		$emailbody = @"
<html>
<head>
<meta http-equiv=Content-Type content="text/html; charset=windows-1252">
<meta name=Generator content="Microsoft Word 14 (filtered)">
<style>
<!--
 /* Font Definitions */
 @font-face
	{font-family:"Cambria Math";
	panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
	{font-family:Calibri;
	panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
	{font-family:Tahoma;
	panose-1:2 11 6 4 3 5 4 4 2 4;}
 /* Style Definitions */
 p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin-top:0in;
	margin-right:0in;
	margin-bottom:10.0pt;
	margin-left:0in;
	line-height:115%;
	font-size:11.0pt;
	font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
	{color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{color:purple;
	text-decoration:underline;}
p.MsoAcetate, li.MsoAcetate, div.MsoAcetate
	{mso-style-link:"Balloon Text Char";
	margin:0in;
	margin-bottom:.0001pt;
	font-size:8.0pt;
	font-family:"Tahoma","sans-serif";}
span.MsoPlaceholderText
	{color:gray;}
span.BalloonTextChar
	{mso-style-name:"Balloon Text Char";
	mso-style-link:"Balloon Text";
	font-family:"Tahoma","sans-serif";}
.MsoChpDefault
	{font-family:"Calibri","sans-serif";}
.MsoPapDefault
	{margin-bottom:10.0pt;
	line-height:115%;}
@page WordSection1
	{size:8.5in 11.0in;
	margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
	{page:WordSection1;}
-->
</style>
</head>
<body lang=EN-US link=blue vlink=purple>
<div class=WordSection1>
<p class=MsoNormal>CHW Messaging Reporting</p>
<p class=MsoNormal>$($emailMessage)</p>
<div class=MsoNormal align=center style='text-align:center'>
<hr size=2 width="100%" align=center>
</div>
<p class=MsoNormal><span style='font-size:9.0pt;line-height:115%;color:#BFBFBF'>Scripted
in Powershell by <a href="mailto:david_f_ray@dell.com"><span style='color:#0000BF'>David
Ray</span></a> {$($MyInvocation.MyCommand.Name)}</span></p>
<p class=MsoNormal>&nbsp;</p>
</div>
</body>
</html>
"@

		if ($OneOffEmailAddr -eq "default") {
			$to = $defaultTo
			$cc = $defaultCc
			$bcc = $defaultBcc
			}
		else {
			$to = $OneOffEmailAddr
			$cc = ""
			$bcc = ""
		}

		$mailer = new-object Net.Mail.SMTPclient($SMTPserver)
		$msg = new-object Net.Mail.MailMessage
		$msg.From = new-object System.Net.Mail.MailAddress($defaultFrom)
		$msg.Subject = $subject
		$msg.Body = $emailbody
		$to | foreach { $msg.To.Add($_)}
		if ($cc.Length -gt 0) {$cc | ForEach-Object {$msg.cc.Add($_)}}
		if ($bcc.Length -gt 0) {$bcc | ForEach-Object {$msg.bcc.Add($_)}}
		$attachment = new-object Net.Mail.Attachment("$($reportPath)\$($reportName).xlsb")
		$msg.attachments.add($attachment)
		$msg.IsBodyHtml = $true

		$mailer.send($msg)
	}
